
IF EXISTS (SELECT 1
          FROM SYSOBJECTS
          WHERE  id = OBJECT_ID('USP_List_stp_auth_department')
          AND TYPE IN ('P','PC'))
DROP PROCEDURE USP_List_stp_auth_department
GO
/****** Object:  StoredProcedure [dbo].[USP_List_stp_auth_department]    Script Date: 10/29/2010 23:33:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		AKE
-- Create date: 17:36 18/9/2010
-- =============================================
CREATE PROCEDURE [dbo].[USP_List_stp_auth_department]
	-- Add the parameters for the stored procedure here
	@department_code int,
	@main_system_code int,
	@menu_system_code int,
	@auth_department_status int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    
    
	select
			sub.sub_menu_code ,
			sub.menu_system_code,
			main.main_system_code,
			sub.sub_menu_name ,
			mnu.menu_system_name,
			main.main_system_name,
			dept.department_name ,
			sub.sub_menu_url ,
			m.[type_name] ,
			[status] = (
				select dbo.UFN_GetStatusLabel(sub.sub_menu_status)
			) ,
			auth_department_code
	from
			stp_menu_system as mnu
	join
			stp_sub_menu as sub on sub.menu_system_code = mnu.menu_system_code
	join
			stp_main_system as main on mnu.main_system_code = main.main_system_code
	join
			master_type as m on sub.type_code = m.type_code			
	join
			stp_auth_department as ad on sub.sub_menu_code = ad.sub_menu_code
	join
			stp_department as dept on ad.department_code = dept.department_code
	where
				@department_code in (0,dept.department_code)
			and	@main_system_code in (0,main.main_system_code)
			and @menu_system_code in (0,sub.menu_system_code)
			and	@auth_department_status in (0,ad.auth_department_status)



    
END
GO


